from flask import current_app,Blueprint,request,jsonify,make_response
from xlrd import open_workbook
import os
from utils.myf import myf
from io import BytesIO
from xlrd import open_workbook
from urllib.parse import quote
import xlsxwriter
import datetime
import mimetypes
from utils.mydb import db1
from utils.xtree import xtree
from utils.getdata import getdata
from utils.myes import Es
import json
from utils.myredis import r
from utils.mytoken import token1

bp_company = Blueprint("bp_company",__name__)

@bp_company.route("/test1")
def test():
    str = "请求ip:"+request.remote_addr+"请求url:"+request.url+"错误信息:"+'/n'
    # current_app.logger.info(str)
    # current_app.logger.warning(str)
    current_app.logger.error(str)
    return "ok"



@bp_company.route('/upload',methods=["POST"])
def upload():
    # 获取文件
    file = request.files['file']

    newname = myf.rename(file.filename)

    # 上传文件
    file.save(os.path.join('./static/files/' + newname))
    # 打开文件读取
    name = './static/files/' + newname
    bk = open_workbook(name, encoding_override='utf-8')
    sh = bk.sheets()[0]
    nrows = sh.nrows
    ncols = sh.ncols
    for i in range(1, nrows):
        row_date = sh.row_values(i)
        print(row_date[0])
        print(row_date[1])
    return jsonify({"msg":"ok"})

@bp_company.route('/download',methods=["POST","GET"])
def download():
    # 实例化二进制数据
    out=BytesIO()
    #  创建一个Excel实例
    workbook=xlsxwriter.Workbook(out)
    #  添加一个sheet，add_worksheet()，默认"Sheet1"，可自定义命名，add_worksheet("资产列表")
    table=workbook.add_worksheet()
    #  table.write(行, 列, 字段名)
    #  行、列从 0 开始计数
    #  字段命名根据每个人使用情况而定，插入数据部分也是
    sql="select * from company limit 0,10"
    res=db1.find_all(sql)
    table.write(0,0,"编号")
    table.write(0,1,"名称")
    num=1
    for i in res:
        table.write(num,0,i['id'])
        table.write(num,1,i['name'])
        num += 1
        workbook.close()
    filename=quote("{}.xls".format(datetime.datetime.now().strftime("%Y%m%d%H%M%S")))
    file=make_response(out.getvalue())
    out.close()
    mime_type=mimetypes.guess_type('%s.xls'%filename)[0]
    file.headers['Content-Type']=mime_type
    file.headers["Cache-Control"]="no-cache"
    file.headers['Content-Disposition']="attachment; filename*=utf-8''{}".format(filename)
    return file

import asyncio

@bp_company.route("/group")
def group():
    sql ="select * from company"
    res = db1.find_all(sql)
    data = xtree(res)
    return jsonify({"code":200,"list":data})

@bp_company.route("/addcompany",methods=["POST"])
def addcompany():
    data = getdata()
    sql = "insert into company(name,pid) values('%s',%d)"%(data["name"],int(data["pid"]))
    db1.update(sql)
    return jsonify({"code":200,"msg":"ok"})

@bp_company.route("/userslist")
def userslist():
    sql ="select * from users"
    res = db1.find_all(sql)

    return jsonify({"code":200,"list":res})

@bp_company.route("/deptesinsert")
def deptesinsert():
    es=Es("testes","test-type")
    #插入数据
    sql="select * from users limit 0,10"
    res=db1.find_all(sql)
    for i in res:
        es.insert(i)
    return "ok"
@bp_company.route("/getdatas")
def getdatas():
    test = request.args.get("test")
    es=Es("testes","test-type")
    query={"match":{"name":test}}
    #插入数据
    res = es.find(query)
    lst = []
    for i in res["hits"]["hits"]:
        lst.append(i["_source"])
    print(lst)
    return jsonify({"code":200,"list":lst})

@bp_company.route("/permission")
def permission():
    sql = "select id,name from resources"
    res = db1.find_all(sql)
    print(res)
    return jsonify({"code":200,"list":res})

@bp_company.route("/selectpermission")
def selectpermission():
    userid = request.args.get("userid")
    sql = "select r.id,r.name from user_resource as ur left join resources as r on ur.resource_id=r.id where ur.user_id =%d"%int(userid)
    res = db1.find_all(sql)
    print(res)
    return jsonify({"code": 200, "list": res})



@bp_company.route("/updatePermission",methods=["POST"])
def updatePermission():
    data = getdata()
    sql = "delete from role_resource where role_id=%d"%int(data["id"])
    db1.update(sql)
    for i in json.loads(data["list"]):
        sql1 = "insert into role_resource(role_id,resource_id) values(%d,%d)"%(int(data["id"]),int(i["id"]))
        db1.update(sql1)
    return jsonify({"code":200,"msg":"权限修改成功"})


@bp_company.route("/getrole")
def getrole():
    sql = "select * from roles"
    res = db1.find_all(sql)
    return jsonify({"code":200,"list":res})

@bp_company.route("/getuser")
def getuser():
    roleid = request.args.get("roleid")
    sql = "select * from users right join user_role on users.id=user_role.user_id where user_role.role_id=%d"%int(roleid)
    res = db1.find_all(sql)
    return jsonify({"code":200,"list":res})

@bp_company.route("/get_user_resource")
def get_user_resource():
    roleid = request.args.get("roleid")
    sql = "select r.id,r.name from resources as r right join role_resource as rr on r.id=rr.resource_id where role_id=%d" % int(roleid)
    res = db1.find_all(sql)
    return jsonify({"code":200,"list":res})

@bp_company.route("/vfshow")
def vdshow():
    data = getdata()
    sql = "select from user_role as ur left join role_resource as rr on ur.role_id=rr.role_id left join resource as r on rr.role_id=r.id left join resource as pr on r.pid= pr.id where ur.user_id=%d and type=1"%(int(data["user_id"]))
    res = db1.find_all(sql)
    return jsonify({"code":200,"list":res})

# import datetime
import functools

# def outer(func):
#     @functools.wraps
#     def inner(*args,**kwargs):
#         token = request.headers["Authorization"]
#         user = token1.decode_jwt(token,"123")
#         sql = "select * from users as u join user_role as ur on u.id=ur.user_id where u.id=%d"%int(user["uid"])
#         res = db1.find(sql)
#
#         if str(r.hget_key("abac", "level"), encoding="utf8"):
#
#         if request.method!="get":
#             return "method is not allowed"
#         nowtime = datetime.datetime.now().strftime("%Y-%m-%d")
#         stime = str(r.hget_key("abac", "time"),encoding="utf8")[0]
#         etime = str(r.hget_key("abac", "time"),encoding="utf8")[1]
#         if nowtime < stime and nowtime > etime:
#             return "time is not allowed"
#         if request.remote_addr != str(r.hget_key("abac","ip"),encoding="utf8"):
#             return "ip is not allowed"
#         func(*args, **kwargs)
#     return inner


@bp_company.route("/abacpermission",methods=["GET","POST","DELETE","PUT"])
# @outer
def abacpermission():
    key = "abac"
    res = r.hget_keys(key)
    lst = []
    for i,j in res.items():
        lst.append(str(j,encoding="utf8"))
    print(lst)
    return "ok"
@bp_company.route('/getcomanyuser')
def getcomanyuser():
    #获取所有公司及所有部门
    sql = "select * from dept"
    res = db1.find_all(sql)
    mes = getdatas(res)
    return jsonify({'code':200,'list':mes})
